using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.Security.Application;

namespace SecurityLinkUp
{
    public class ChapterData
    {
        // gets a count of the chapters in a given status (or all if status = any)
        public static int GetChapterCount(CommonData.Status status)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) communities
                    SqlCommand cmd = new SqlCommand("GetChapterCount", conn);
                    if (status == CommonData.Status.ANY)
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", status.ToString()));
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        return int.Parse(reader["count"].ToString());
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return 0;
            }   
        }

        // creates a chapter object for the supplied chapter ID
        public static Chapter GetChapter(Guid ChapterID)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) chapters
                    SqlCommand cmd = new SqlCommand("GetChapterFromID", conn);
                    cmd.Parameters.Add(new SqlParameter("@ID", ChapterID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        Chapter ret = CreateChapterFromDBRecord(reader);
                        return ret;
                    }
                    else
                    {
                        return null;
                    }
                }                   
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // creates an arraylist of chapters of the given status (or all if status = any)
        public static ArrayList GetChapters(CommonData.Status status)
        {
            ArrayList ret = new ArrayList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the chapters based on their status
                    SqlCommand cmd = new SqlCommand("GetChapters", conn);
                    if (status == CommonData.Status.ANY)
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", status.ToString()));
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        //create a chapter object and add it to the return array
                        Chapter c = CreateChapterFromDBRecord(reader);
                        if (c != null)
                        {
                            ret.Add(c);
                        }
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }


        // creates and arraylist of chapters that have the communiy (from it's ID) as their parent
        public static ArrayList GetChaptersFromCommunityID(Guid CommunityID, CommonData.Status status)
        {
            ArrayList ret = new ArrayList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) chapters
                    SqlCommand cmd = new SqlCommand("GetChaptersFromCommunityID", conn);
                    cmd.Parameters.Add(new SqlParameter("@CommunityID", CommunityID.ToString()));
                    if (status == CommonData.Status.ANY)
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", DBNull.Value));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@status", status.ToString()));
                    }
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        //create a chapter object and add it to the return array
                        Chapter c = CreateChapterFromDBRecord(reader);
                        ret.Add(c);
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            } 
        }

        // gets the chapters a user admins
        public static ArrayList GetUserAdminChapters(Guid UserID)
        {
            ArrayList ret = new ArrayList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();

                    // setup the query to get all the chapters
                    SqlCommand cmd = new SqlCommand("GetUserAdminChapters", conn);
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        //create a chapter object and add it to the return array
                        Chapter c = CreateChapterFromDBRecord(reader);
                        ret.Add(c);
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // gets all the admins for a chapter
        public static SortedList GetChapterAdmins(Guid ChapterID)
        {
            SortedList ret = new SortedList();

            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();

                    // setup the query to get all the chapters
                    SqlCommand cmd = new SqlCommand("GetChapterAdmins", conn);
                    cmd.Parameters.Add(new SqlParameter("@ChapterID", ChapterID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        // get the userID
                        Guid UserID = new Guid(reader["userid"].ToString());

                        //create a user object and add it to the return array
                        User u = UserData.GetUser(UserID);
                        ret.Add(u.uid, u);
                    }
                    return ret;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }
        }

        // add a user as an admin for a chapter
        public static bool AddChapterAdmin(Guid ChapterID, Guid UserID)
        {
            try
            {
                // check that the calling user has the permissions to do this operation
                MembershipUser currentusr = Membership.GetUser();
                if (Security.IsAdminUser())
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("AddChapterAdmin", conn);
                        cmd.Parameters.Add(new SqlParameter("@ChapterID", ChapterID.ToString()));
                        cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();

                        // if the user isnt a chapteradmin already, add them to the role group
                        MembershipUser usr = Membership.GetUser(UserID);
                        if (!Roles.IsUserInRole(usr.UserName, "ChapterAdmin"))
                        {
                            Roles.AddUserToRole(usr.UserName, "ChapterAdmin");
                        }
                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        private static bool IsChapterAdminOrSuperAdmin(Guid ChapterID, Guid UserID)
        {
            // get the user
            MembershipUser usr = Membership.GetUser(UserID);

            if (Roles.IsUserInRole(usr.UserName, "SuperAdmin"))
            {
                return true;
            }

            if (Roles.IsUserInRole("ChapterAdmin"))
            {
                // do DB query to see if user is in the chapter admin table
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("IsChapterAdmin", conn);
                    cmd.Parameters.Add(new SqlParameter("@ChapterID", ChapterID.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            return false;
        }

        // removes a user as an admin for a chapter
        public static bool RemoveChapterAdmin(Guid ChapterID, Guid UserID)
        {
            try
            {
                // check that the calling user has the permissions to do this operation
                if (Security.IsAdminUser())
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("RemoveChapterAdmin", conn);
                        cmd.Parameters.Add(new SqlParameter("@ChapterID", ChapterID.ToString()));
                        cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();

                        // Is the user an admin of any other chapters? If they arent,
                        // remove their admin role
                        ArrayList chapters = GetUserAdminChapters(UserID);
                        if (chapters.Count == 0)
                        {
                            MembershipUser usr = Membership.GetUser(UserID);
                            Roles.RemoveUserFromRole(usr.UserName, "ChapterAdmin");
                        }

                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        // gets a count of the number of members of a chapter
        public static int GetChapterMembersCount(Guid ChapterID)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    // setup the query to get all the (active) communities
                    SqlCommand cmd = new SqlCommand("GetChapterMembersCount", conn);
                    cmd.Parameters.Add(new SqlParameter("@ChapterID", ChapterID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        return int.Parse(reader["count"].ToString());
                    }
                    else
                    {
                        return 0;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return 0;
            }
        }

        // delete a chapter
        public static bool DeleteChapter(Guid ChapterID)
        {
            try
            {
                if (Roles.IsUserInRole("SuperAdmin"))
                {
                    string connstr = Security.GetDBConnectionString();
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        SqlCommand cmd = new SqlCommand("DeleteChapter", conn);
                        cmd.Parameters.Add(new SqlParameter("@ID", ChapterID.ToString()));
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.ExecuteNonQuery();
                        return true;
                    }
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        public static bool IsUserChapterMember(Guid ChapterID, Guid UserID)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("IsUserChapterMember", conn);
                    cmd.Parameters.Add(new SqlParameter("@ChapterId", ChapterID.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }

        public static bool JoinChapter(Guid ChapterID, Guid UserID)
        {
            try
            {
                string connstr = Security.GetDBConnectionString();
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("JoinChapter", conn);
                    cmd.Parameters.Add(new SqlParameter("@ChapterId", ChapterID.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@UserID", UserID.ToString()));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return false;
            }
        }



        #region Get/Save chapter object from/to database
        // TODO: we could probably do with limiting the scope of these functions so that
        // they cant be called directly, and you have to go through the main operation
        // functions above.  However, because we are calling these functions static, we can't
        // move them from "public"?

        // Creates a chapter object from a database record
        public static Chapter CreateChapterFromDBRecord(SqlDataReader data)
        {
            //create a chapter object
            try
            {

                if ((data["geolat"] == DBNull.Value) || (data["geolong"] == DBNull.Value))
                {
                    // create chapter using the "no position" method
                    Chapter c = new Chapter(new Guid(data["uid"].ToString()),
                                            new Guid(data["communityid"].ToString()),
                                            data["name"].ToString(),
                                            data["description"].ToString(),
                                            (CommonData.Status)Enum.Parse(typeof(CommonData.Status),data["status"].ToString(), true),
                                            data["statusnotes"].ToString());
                    return c;
                }
                else
                {
                    // creat the community with position
                    Chapter c = new Chapter(new Guid(data["uid"].ToString()),
                                            new Guid(data["communityid"].ToString()),
                                            data["name"].ToString(),
                                            data["description"].ToString(),
                                            (CommonData.Status)Enum.Parse(typeof(CommonData.Status), data["status"].ToString(), true),
                                            data["statusnotes"].ToString());

                            c.SetGeoData(
                                data["geoarea"].ToString(),
                                double.Parse(data["geolat"].ToString()),
                                double.Parse(data["geolong"].ToString())
                                );
       
                    return c;
                }
                
            }
            catch (Exception ex)
            {
                Log.LogException(ex);
                return null;
            }    
        }

        // saves a chapter object to the database
        public static string SaveChapterToDB(Chapter c)
        {
            string connstr = Security.GetDBConnectionString();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                try
                {
                    conn.Open();

                    // check to see if the chaptername is unique if it's a "new" chapter
                    if (c.uid == Guid.Empty)
                    {
                        SqlCommand checkcmd = new SqlCommand("CheckUniqueChapterName", conn);
                        checkcmd.Parameters.Add(new SqlParameter("@name", c.name));
                        checkcmd.CommandType = CommandType.StoredProcedure;
                        SqlDataReader checkreader = checkcmd.ExecuteReader();
                        checkreader.Read();
                        if (checkreader["result"].ToString() != "UNIQUE")
                        {
                            return "Chapter name is not unique";
                        }
                        checkreader.Close();
                    }

                    // save the chapter
                    SqlCommand cmd = new SqlCommand("SaveChapter", conn);
                    if (c.uid != Guid.Empty)
                    {
                        cmd.Parameters.Add(new SqlParameter("@ID", c.uid.ToString()));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@ID", DBNull.Value));
                    }
                    cmd.Parameters.Add(new SqlParameter("@CommunityID", c.parentcommunityid));
                    cmd.Parameters.Add(new SqlParameter("@name", c.name));
                    cmd.Parameters.Add(new SqlParameter("@description", c.description));
                    cmd.Parameters.Add(new SqlParameter("@status", c.status.ToString()));
                    cmd.Parameters.Add(new SqlParameter("@statusnotes", c.statusnotes));
                    if (c.HasPosition)
                    {
                        cmd.Parameters.Add(new SqlParameter("@geoarea", c.geoarea));
                        cmd.Parameters.Add(new SqlParameter("@geolat", c.geolat));
                        cmd.Parameters.Add(new SqlParameter("@geolong", c.geolong));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter("@geoarea", DBNull.Value));
                        cmd.Parameters.Add(new SqlParameter("@geolat", DBNull.Value));
                        cmd.Parameters.Add(new SqlParameter("@geolong", DBNull.Value));
                    }
                    cmd.Parameters.Add(new SqlParameter("@CreatedBy", Membership.GetUser().ProviderUserKey));
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                    return "";
                }
                catch (Exception ex)
                {
                    Log.LogException(ex);
                    return "Error saving";
                }
            }
        }

        #endregion


    }


    public class Chapter
    {
        private Guid m_uid;
        private Guid m_parentcommunityid;
        private string m_name;
        private string m_description;
        private CommonData.Status m_status;
        private string m_statusnotes;
        private string m_geoarea;
        private double m_geolat;
        private double m_geolong;
        private bool m_hasposition;

        // constructor version with no geo position
        public Chapter(Guid uid, Guid parentcommunityid, string name, string description, CommonData.Status status, string statusnotes)
        {
            m_uid = uid;
            m_parentcommunityid = parentcommunityid;
            m_name = name;
            m_description = description;
            m_status = status;
            m_statusnotes = statusnotes;
            m_hasposition = false;
        }

        public void SetGeoData(string GeoArea, double GeoLat, double GeoLong)
        {
            m_geoarea = GeoArea;
            m_geolat = GeoLat;
            m_geolong = GeoLong;
            m_hasposition = true;
        }

        public void SetStatus(CommonData.Status status, string notes)
        {
            m_status = status;
            m_statusnotes = notes;
        }

        public Guid uid
        {
            get { return m_uid; }
        }

        public Guid parentcommunityid
        {
            get { return m_parentcommunityid; }
        }

        public string name
        {
            get { return AntiXss.HtmlEncode(m_name); }
            set { m_name = value; }
        }

        public string description
        {
            get { return AntiXss.HtmlEncode(m_description); }
            set { m_description = value; }
        }

        public CommonData.Status status
        {
            get { return m_status; }
        }

        public string statusnotes
        {
            get { return AntiXss.HtmlEncode(m_statusnotes); }
        }

        public string geoarea
        {
            get { return m_geoarea; }
        }

        public double geolat
        {
            get { return m_geolat; }
        }

        public double geolong
        {
            get { return m_geolong; }
        }

        public bool HasPosition
        {
            get { return m_hasposition; }
        }
    }
}
